package org.manekineko.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.manekineko.dao.BaseDAO;
import org.manekineko.dao.ResultadoDAO;
import org.manekineko.utils.DatabaseUtils;

public class ResultadoMSDAO extends BaseDAO implements ResultadoDAO {

	public void registrarAtrasos() {
		int nuUltimoSorteio = buscarNumeroUltimoSorteio();

		int nuUltimoSorteioAtraso = buscarNumertoUltimoSorteioAtraso();

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			conn = getDataSource().getConnection();

			for (int i = nuUltimoSorteioAtraso; i < nuUltimoSorteio; i++) {

				pstmt = conn.prepareStatement("SELECT * FROM TB_MS_ATRASOS where nu_sorteio = ?");
				pstmt.setInt(1, i);

				rs = pstmt.executeQuery();

				// contabiliza os atrasos, +1 para cada ocorrencia
				Map<Integer, Integer> p = new HashMap<Integer, Integer>();
				while (rs.next()) {
					p.put(rs.getInt(2), rs.getInt(3) + 1);
				}

				// resultados do sorteio posterior, para esses, zerar o atraso
				int[] dezenas = resultadoSorteio(i + 1);
				for (int j = 0; j < dezenas.length; j++) {
					p.put(dezenas[j], 0);
				}

				pstmt.close();

				pstmt = conn.prepareStatement("INSERT INTO TB_MS_ATRASOS VALUES(?,?,?)");
				for (int key : p.keySet()) {
					pstmt.setInt(1, i + 1);
					pstmt.setInt(2, key);
					pstmt.setInt(3, p.get(key));
					pstmt.addBatch();
				}

				pstmt.executeBatch();

				System.out.println(p);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DatabaseUtils.close(rs, pstmt, conn);
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.manekineko.dao.ResultadoDAO#buscarNumeroUltimoSorteio()
	 */
	public int buscarNumeroUltimoSorteio() {
		int result = 0;

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {

			conn = getDataSource().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT max(nu_sorteio) FROM TB_MS_SORTEIOS");
			result = rs.getInt(1);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DatabaseUtils.close(rs, stmt, conn);
		}

		return result;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.manekineko.dao.ResultadoDAO#salvarSorteio(int,
	 * java.lang.Integer[])
	 */
	public void salvarSorteio(int numeroSorteio, Integer[] resultado) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			conn = getDataSource().getConnection();

			pstmt = conn.prepareStatement("INSERT INTO TB_MS_SORTEIOS VALUES(?,?,?,?,?,?,?)");

			int p = 0;

			pstmt.setInt(++p, numeroSorteio);
			for (int i = 0; i < resultado.length; i++) {
				pstmt.setInt(++p, resultado[i]);
			}

			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DatabaseUtils.close(rs, pstmt, conn);
		}
	}

	public int buscarNumertoUltimoSorteioAtraso() {
		int result = 0;

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {

			conn = getDataSource().getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT MAX(nu_sorteio) FROM TB_MS_ATRASOS");
			result = rs.getInt(1);

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DatabaseUtils.close(rs, stmt, conn);
		}

		return result;
	}

	public static void main(String[] args) {
		ResultadoMSDAO dao = new ResultadoMSDAO();
		dao.atualizarHashSorteios();
	}

	public void exportarAtrasos() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			conn = getDataSource().getConnection();
			pstmt = conn.prepareStatement("SELECT distinct nu_sorteio FROM TB_MS_ATRASOS order by nu_sorteio");

			rs = pstmt.executeQuery();

			List<Integer> sorteios = new ArrayList<Integer>();
			while (rs.next()) {
				sorteios.add(rs.getInt(1));
			}

			rs.close();
			pstmt.close();

			pstmt = conn.prepareStatement("SELECT * FROM TB_MS_ATRASOS where nu_sorteio = ?");
			for (Integer nuSorteio : sorteios) {
				StringBuilder sb = new StringBuilder();
				sb.append(nuSorteio);

				pstmt.setInt(1, nuSorteio);
				rs = pstmt.executeQuery();
				while (rs.next()) {
					sb.append("," + rs.getInt(3));
				}
				System.out.println(sb.toString());
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DatabaseUtils.close(rs, pstmt, conn);
		}
	}

	public void atualizarHashSorteios() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			conn = getDataSource().getConnection();
			pstmt = conn.prepareStatement("select nu_sorteio, d01||'#'||d02||'#'||d03||'#'||d04||'#'||d05||'#'||d06 "
					+ "FROM TB_MS_SORTEIOS where hash is null order by nu_sorteio");

			rs = pstmt.executeQuery();

			Map<Integer, String> p = new HashMap<Integer, String>();
			while (rs.next()) {
				p.put(rs.getInt(1), DatabaseUtils.hash(rs.getString(2)));
			}

			System.out.println(p.size());

			rs.close();
			pstmt.close();

			pstmt = conn.prepareStatement("update TB_MS_SORTEIOS set hash = ? where nu_sorteio = ?");
			for (Integer key : p.keySet()) {

				pstmt.setString(1, p.get(key));
				pstmt.setInt(2, key);

				pstmt.addBatch();

				if (key % 10 == 0) {
					pstmt.executeBatch();
					System.out.println(key);
				}
			}

			pstmt.executeBatch();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DatabaseUtils.close(rs, pstmt, conn);
		}
	}

	/**
	 * 
	 */
	public int[] resultadoUltimoSorteio() {
		return resultadoSorteio(buscarNumeroUltimoSorteio());
	}

	/**
	 * 
	 */
	public int[] resultadoSorteio(int numeroSorteio) {
		int[] result = new int[6];

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			conn = getDataSource().getConnection();

			StringBuilder sb = new StringBuilder();
			sb.append(" select * FROM TB_MS_SORTEIOS");
			sb.append(" where nu_sorteio = ?");

			pstmt = conn.prepareStatement(sb.toString());
			pstmt.setInt(1, numeroSorteio);

			rs = pstmt.executeQuery();

			while (rs.next()) {
				result[0] = rs.getInt(2);
				result[1] = rs.getInt(3);
				result[2] = rs.getInt(4);
				result[3] = rs.getInt(5);
				result[4] = rs.getInt(6);
				result[5] = rs.getInt(7);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DatabaseUtils.close(rs, pstmt, conn);
		}

		return result;
	}

	@Override
	public boolean existeSorteio(String hash) {
		boolean result = false;

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			conn = getDataSource().getConnection();

			pstmt = conn.prepareStatement("SELECT * FROM TB_MS_SORTEIOS where hash = ?");
			pstmt.setString(1, hash);

			rs = pstmt.executeQuery();
			
			result = rs.next();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DatabaseUtils.close(rs, pstmt, conn);
		}

		return result;
	}

	@Override
	public boolean dezenaEmAtraso(int qtSorteios, int dezena) {
		boolean result = false;

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {

			conn = getDataSource().getConnection();

			StringBuilder sb = new StringBuilder();
			sb.append("select nu_dezena FROM TB_MS_ATRASOS");
			sb.append(" where nu_sorteio = (");
			sb.append("   SELECT MAX(nu_sorteio) FROM TB_MS_ATRASOS");
			sb.append(" ) and qt_atraso > ?");
			sb.append(" and nu_dezena = ?");
			
			pstmt = conn.prepareStatement(sb.toString());
			pstmt.setInt(1, qtSorteios);
			pstmt.setInt(2, dezena);

			rs = pstmt.executeQuery();
			
			result = rs.next();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DatabaseUtils.close(rs, pstmt, conn);
		}

		return result;
	}
}
